package scct.ZhuZhouXing.dao;

import scct.ZhuZhouXing.model.Product;
import scct.ZhuZhouXing.utils.DBUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ProductDao {
    //查询
    public List<Product> getProduct() {
        List<Product> list = new ArrayList<>();
        Connection conn = DBUtils.getConnection();
        String sql = "select * from sp_product";
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                int pid = rs.getInt("pid");
                String pname = rs.getString("pname");
                int pprice = rs.getInt("pprice");
                int pinventory = rs.getInt("pinventory");
                String pleibie = rs.getString("pleibie");
                String pbrief = rs.getString("pbrief");
                list.add(new Product(pid, pname, pprice, pinventory, pleibie, pbrief));
            }
            DBUtils.closeConnection(conn);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return list;
    }
    //查询单个条件
    public List<Product> getSelect(String name) {
    List<Product> list=new ArrayList<>();
    Connection conn =DBUtils.getConnection();
    String sql="select  * from  sp_product where pname like '%"+name+"%'";
    try {
        PreparedStatement pstmt=conn.prepareStatement(sql);
        ResultSet rs =pstmt.executeQuery();
        while (rs.next()){
            int pid = rs.getInt("pid");
            String pname = rs.getString(2);
            int pprice = rs.getInt("pprice");
            int pinventory = rs.getInt("pinventory");
            String pleibie = rs.getString("pleibie");
            String pbrief = rs.getString("pbrief");
            list.add(new Product(pid, pname, pprice, pinventory, pleibie, pbrief));
        }
        DBUtils.closeConnection(conn);
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
    return list;
    }
    //筛选
    public List<Product> getScreen(String Screen) {
        List<Product> list=new ArrayList<>();
        Connection conn =DBUtils.getConnection();
        String sql="select  * from  sp_product  where pleibie like '%"+Screen+"%'";
        try {
            PreparedStatement pstmt=conn.prepareStatement(sql);
            ResultSet rs =pstmt.executeQuery();
            while (rs.next()){
                int pid = rs.getInt("pid");
                String pname = rs.getString("pname");
                int pprice = rs.getInt("pprice");
                int pinventory = rs.getInt("pinventory");
                String pleibie = rs.getString(5);
                String pbrief = rs.getString("pbrief");
                list.add(new Product(pid, pname, pprice, pinventory, pleibie, pbrief));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return list;
    }

    //增加
    public boolean addProduct(String pname, int pprice, int pinventory, String pleibie, String pbrief) {
        int result = 0;
        Connection conn = DBUtils.getConnection();
        String insert_sql = "insert into  sp_product values (null,?,?,?,?,?);";
        try {
            PreparedStatement pstmt = conn.prepareStatement(insert_sql);
            pstmt.setString(1, pname);
            pstmt.setInt(2, pprice);
            pstmt.setInt(3, pinventory);
            pstmt.setString(4, pleibie);
            pstmt.setString(5, pbrief);
            result = pstmt.executeUpdate();
            DBUtils.closeConnection(conn);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return result == 1;
    }

    //商品判断是否重复
    public boolean isDupname(String name) {
        int count = 0;
        Connection conn = DBUtils.getConnection();
        String sql = "select * from sp_product where pname=?";
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, name);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                count = 1;
            }
            DBUtils.closeConnection(conn);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return count > 0;
    }

    //修改
    public boolean updateProduct(int pid, String pname, int pprice, int pinventory, String pleibie, String pbrief) {
        int count = 0;
        Connection conn = DBUtils.getConnection();
        String update_sql = "update sp_product set pname=?,pprice=?,pinventory=?,pleibie=?,pbrief =?  where pid=?";
        try {
            PreparedStatement pstmt = conn.prepareStatement(update_sql);
            pstmt.setString(1, pname);
            pstmt.setInt(2, pprice);
            pstmt.setInt(3, pinventory);
            pstmt.setString(4, pleibie);
            pstmt.setString(5, pbrief);
            pstmt.setInt(6, pid);
            count = pstmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return count > 0;
    }

    //根据点击用户点击修改显示修改内容
    public Product getProductByid(int pid) {
        Product product = null;
        Connection conn = DBUtils.getConnection();
        String sql = "select  * from  sp_product where pid=?";
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, pid);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                String pname = rs.getString("pname");
                int pprice = rs.getInt("pprice");
                int pinventory = rs.getInt("pinventory");
                String pleibie = rs.getString("pleibie");
                String pbrief = rs.getString("pbrief");
                product = new Product(pid, pname, pprice, pinventory, pleibie, pbrief);
            }
            DBUtils.closeConnection(conn);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return product;
    }

    //删除
    public boolean deProduct(int id) {
        int num = 0;
        Connection conn = DBUtils.getConnection();
        String de_sql = "delete  from sp_product where pid=?";
        try {
            PreparedStatement pstmt = conn.prepareStatement(de_sql);
            pstmt.setInt(1, id);
            num = pstmt.executeUpdate();
            DBUtils.closeConnection(conn);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return num > 0;
    }



}
